package ch.zbw.pra.sysventory.database.jdbcTemplate;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.sql.DataSource;

import org.joda.time.LocalDate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.mysql.jdbc.Statement;

import ch.zbw.pra.sysventory.database.mapper.ScanversionMapper;
import ch.zbw.pra.sysventory.model.Scanversion;
import ch.zbw.pra.sysventory.utils.DateUtils;

/**
 * Greift auf die Datenbank zu und delegiert das Mapping der Tabellenspalten an den RowMapper
 * 
 * @author Ralph Koster
 */

public class ScanversionJDBCTemplate implements Serializable {

	private static final long serialVersionUID = -82206843168070601L;
	private static final Logger LOG = LoggerFactory.getLogger(ScanversionJDBCTemplate.class);
	private JdbcTemplate jdbcTemplateObject;
	
	private static final String INSERT_SQL = "INSERT INTO Scanversion (scanVersion, scanDateStart, scanDateEnd) VALUES(?,?,?)";

	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);
	}
	
	public Scanversion getLastScanversion() {
		Scanversion scanversion = null;
		String SQL = "SELECT * FROM scanversion WHERE scanID_pk=(SELECT MAX(scanID_pk) FROM scanversion)";
		List<Scanversion> scanversionen = jdbcTemplateObject.query(SQL,
				new ScanversionMapper());
		if(null != scanversionen && scanversionen.size() == 1) {
			scanversion = scanversionen.get(0);
		} else {
			scanversion = new Scanversion();
			LOG.debug("Last scanversion not found!! Have a look to the database");
		}
		return scanversion;
	}

	public Integer getNumberOfScansInTimerange(HashMap<String, LocalDate> timerange) {
		String SQL = "SELECT COUNT(*) FROM scanversion WHERE scanDateEnd BETWEEN ? AND ?";
		Timestamp dateFrom = DateUtils.convertLocalDateToTimestamp(timerange.get("dateFrom"));
		Timestamp dateTo = DateUtils.convertLocalDateToTimestamp(timerange.get("dateTo"));
		Integer numberOfScans = jdbcTemplateObject.queryForObject(SQL, Integer.class, dateFrom, dateTo);
		LOG.debug("Number of Scans: " + numberOfScans);
		return numberOfScans;
	}

	public List<Scanversion> getAllScanversions() {
		String SQL = "SELECT * FROM Scanversion";
		List<Scanversion> scanversions = new ArrayList<Scanversion>();
		try{
			scanversions = jdbcTemplateObject.query(SQL, new ScanversionMapper());
		} catch (DataAccessException e){
			LOG.debug("Failed to load a list of Scanversions");
			LOG.error(e.toString());
		}
		LOG.debug("Loaded a list of Scanversions");
		return scanversions;
	}

	public Integer insertScanversion(final Scanversion scanversion) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{
		jdbcTemplateObject.update(new PreparedStatementCreator() { 
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { 
				PreparedStatement ps = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS); 
					ps.setLong(1, scanversion.getNumberOfScanversion());
					ps.setTimestamp(2, scanversion.getScanStartTimestamp()); 
					ps.setTimestamp(3, scanversion.getScanEndeTimestamp()); 
					return ps; 
				} 
			}, keyHolder);
		}catch(DataAccessException e){
			LOG.debug("Failed to insert Scanversion with ID "+scanversion.getId());
			LOG.error(e.toString());
		}
		LOG.debug("Inserted scanversion with id: " + keyHolder.getKey());
		return keyHolder.getKey().intValue();
	}
}
